package com.apes.scm.contract.repository;

import com.apes.framework.jpa.repository.CustomRepository;
import com.apes.scm.contract.model.Contract;
import com.apes.scm.masterdata.party.model.PartyRole;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author xul
 * @create 2018-04-10 16:07
 */
public interface ContractRepository extends CustomRepository<Contract, String> {

    @Query(value = "" +
            "SELECT PARTNER_ID " +
            "  FROM ACC_CONTRACT " +
            " WHERE VALID_FROM <= TRUNC(SYSDATE) " +
            "   AND VALID_PERIOD >= TRUNC(SYSDATE) " +
            "   AND COMPANY_ID = '1001' " +
            "   AND STATE = 'approve' " +
            "   AND PARTNER_ID IN ?1 " +
            "   AND CONTRACT_TYPE = ?2 ", nativeQuery = true)
    List<Map> stockGapDumpFindContract(List supplierIds, String contractType);

    @Query(value = "" +
            "SELECT PARTNER_ID " +
            "  FROM ACC_CONTRACT " +
            " WHERE VALID_FROM <= TRUNC(SYSDATE) " +
            "   AND VALID_PERIOD >= TRUNC(SYSDATE) " +
            "   AND STATE = 'approve' " +
            "   AND PARTNER_ID IN ?1 ", nativeQuery = true)
    List<Map> stockGapDumpFindContract(List supplierIds);

    @Query("select T from Contract T where 1 = 1 and (id like %:value%  or name like %:value%) ")
    Page<Contract> find(@Param("value") String value, Pageable pageable);

    @Query("SELECT t from Contract t " +
            " where t.company.id = :companyId " +
            " and t.partner.id = :partnerId " +
            " and t.validPeriod > :currentTime " +
            " and t.approveDate is not null " +
            " and t.repealDate is null")
    List<Contract> findAllByCompanyAndPartner(@Param("companyId") String companyId, @Param("partnerId") String partnerId,
                                              @Param("currentTime") Date currentTime);

    @Query("SELECT t from Contract t where t.company.id = :companyId and t.partner.id = :partnerId ")
    List<Contract> findAllForSaleFlow(@Param("companyId") String companyId, @Param("partnerId") String partnerId);

    /**
     * fetch data by partner Id and dept Id and company id and current time
     *
     * @param companyId   company id
     * @param partnerId   partner id
     * @param deptId      dept id
     * @param currentTime current time
     * @return Contract
     */
    @Query("SELECT t from Contract t " +
            " where t.company.id = :companyId " +
            " and t.partner.id = :partnerId " +
            " and t.dept.id =:deptId " +
            " and t.validPeriod > :currentTime " +
            " and t.approveDate is not null and t.contractType.id ='Contract.contractType.customer' and (t.state='approve' or t.state='extension')")
    Contract findAllByCompanyAndPartnerAndDept(@Param("companyId") String companyId, @Param("partnerId") String partnerId,
                                               @Param("deptId") String deptId,
                                               @Param("currentTime") Date currentTime);

    @Query("select T from Contract T where 1 = 1 and T.contractType.code = :code and T.validPeriod >= :date and T.repealDate is null and T.approveDate is not null ")
    Page<Contract> findContractForDiapatch(@Param("code") String code,@Param("date") Date currentDate, Pageable pageable);

    @Query(value = " SELECT A.*, (SELECT COUNT(*) FROM V_GET_30EFFECTIVE_CONTRACT) TOTAL FROM V_GET_30EFFECTIVE_CONTRACT A ", nativeQuery = true)
    List<Map> query30EffectiveContract();

    @Query(value = "" +
            "select * " +
            "  from ACC_CONTRACT J " +
            " WHERE COMPANY_ID IN :companyId " +
            "   and CONTRACT_TYPE = :contractTypeId " +
            "   and STATE in ('approve', 'extension') " +
            "   and TRUNC(VALID_PERIOD) >= TRUNC(SYSDATE) " +
            "   and (PARTNER_ID LIKE %:partnerId% OR NAME LIKE %:contractName% OR ID LIKE %:contractId% " +
            "        OR EXISTS (SELECT 1 FROM MAS_PARTY H, MAS_PARTY_ROLE K WHERE H.ID = K.PARTY_ID AND K.ID = J.PARTNER_ID AND H.NAME LIKE %:partnerName%) ) " +
            "   and PARTNER_ID not in (:notinSupplier) " +
            "   AND NOT EXISTS " +
            " (SELECT 1 " + // 剔除内部合同
            "          FROM MAS_PARTY_ROLE A " +
            "         WHERE ROLE_ID = 'Supplier' " +
            "           AND EXISTS (SELECT COUNT(PARTY_ID), PARTY_ID " +
            "                  FROM MAS_PARTY_ROLE " +
            "                 WHERE PARTY_ID = A.PARTY_ID " +
            "                   AND ROLE_ID IN ('Supplier', 'Company') " +
            "                 GROUP BY PARTY_ID " +
            "                HAVING COUNT(PARTY_ID) > 1) " +
            "           AND A.ID = J.PARTNER_ID) ", nativeQuery = true)
    List<Contract> queryPlatformContract(@Param("companyId") List companyId, @Param("contractTypeId") String contractTypeId, @Param("partnerId") String partnerId, @Param("contractName") String contractName,
                                   @Param("contractId") String contractId, @Param("partnerName") String partnerName, @Param("notinSupplier") List notinSupplier); // 获取协议
    @Query(value = "" +
            "select * " +
            "  from ACC_CONTRACT J " +
            " WHERE COMPANY_ID IN :companyId " +
            "   and CONTRACT_TYPE = :contractTypeId " +
            "   and STATE in ('approve', 'extension') " +
            "   and TRUNC(VALID_PERIOD) >= TRUNC(SYSDATE) " +
            "   and PARTNER_ID in (:partnerId) " +
            "   AND NOT EXISTS " +
            " (SELECT 1 " + // 剔除内部合同
            "          FROM MAS_PARTY_ROLE A " +
            "         WHERE ROLE_ID = 'Supplier' " +
            "           AND EXISTS (SELECT COUNT(PARTY_ID), PARTY_ID " +
            "                  FROM MAS_PARTY_ROLE " +
            "                 WHERE PARTY_ID = A.PARTY_ID " +
            "                   AND ROLE_ID IN ('Supplier', 'Company') " +
            "                 GROUP BY PARTY_ID " +
            "                HAVING COUNT(PARTY_ID) > 1) " +
            "           AND A.ID = J.PARTNER_ID) ", nativeQuery = true)
    List<Contract> accordingSupplierQuery(@Param("companyId") List companyId, @Param("contractTypeId") String contractTypeId, @Param("partnerId") List partnerId); // 获取协议

    @Query(value = "" +
            "select * " +
            "  from ACC_CONTRACT J " +
            " WHERE COMPANY_ID IN :companyId " +
            "   and CONTRACT_TYPE = :contractTypeId " +
            "   and STATE in ('approve', 'extension') " +
            "   and TRUNC(VALID_PERIOD) >= TRUNC(SYSDATE) " +
            "   AND NOT EXISTS " +
            " (SELECT 1 " + // 剔除内部合同
            "          FROM MAS_PARTY_ROLE A " +
            "         WHERE ROLE_ID = 'Supplier' " +
            "           AND EXISTS (SELECT COUNT(PARTY_ID), PARTY_ID " +
            "                  FROM MAS_PARTY_ROLE " +
            "                 WHERE PARTY_ID = A.PARTY_ID " +
            "                   AND ROLE_ID IN ('Supplier', 'Company') " +
            "                 GROUP BY PARTY_ID " +
            "                HAVING COUNT(PARTY_ID) > 1) " +
            "           AND A.ID = J.PARTNER_ID) ", nativeQuery = true)
    List<Contract> accordingSupplierQuery(@Param("companyId") List companyId, @Param("contractTypeId") String contractTypeId); // 获取协议 不需要供应商

    @Query(value = "" +
            "select * " +
            "  from ACC_CONTRACT J " +
            " WHERE COMPANY_ID IN :companyId " +
            "   and CONTRACT_TYPE = :contractTypeId " +
            "   and STATE in ('approve', 'extension') " +
            "   and TRUNC(VALID_PERIOD) >= TRUNC(SYSDATE) " +
            "   and PARTNER_ID in (:partnerId) " +
            "   AND NOT EXISTS " +
            " (SELECT 1 " + // 剔除内部合同
            "          FROM MAS_PARTY_ROLE A " +
            "         WHERE ROLE_ID = 'Supplier' " +
            "           AND EXISTS (SELECT COUNT(PARTY_ID), PARTY_ID " +
            "                  FROM MAS_PARTY_ROLE " +
            "                 WHERE PARTY_ID = A.PARTY_ID " +
            "                   AND ROLE_ID IN ('Supplier', 'Company') " +
            "                 GROUP BY PARTY_ID " +
            "                HAVING COUNT(PARTY_ID) > 1) " +
            "           AND A.ID = J.PARTNER_ID) " +
            " AND J.PARTNER_ID NOT IN (SELECT PARTNER_ID FROM ACC_CONTRACT WHERE CONTRACT_TYPE = 'Contract.contractType.platform' AND VALID_PERIOD >= GET_DATE() AND STATE IN ('approve', 'extension')) ", nativeQuery = true)
    List<Contract> qryValidContract(@Param("companyId") List companyId, @Param("contractTypeId") String contractTypeId, @Param("partnerId") List partnerId);


    @Query(value = "" +
            "select * " +
            "  from ACC_CONTRACT J " +
            " WHERE COMPANY_ID IN :companyId " +
            "   and CONTRACT_TYPE = :contractTypeId " +
            "   and STATE in ('approve', 'extension') " +
            "   and TRUNC(VALID_PERIOD) >= TRUNC(SYSDATE) " +
            "   AND NOT EXISTS " +
            " (SELECT 1 " + // 剔除内部合同
            "          FROM MAS_PARTY_ROLE A " +
            "         WHERE ROLE_ID = 'Supplier' " +
            "           AND EXISTS (SELECT COUNT(PARTY_ID), PARTY_ID " +
            "                  FROM MAS_PARTY_ROLE " +
            "                 WHERE PARTY_ID = A.PARTY_ID " +
            "                   AND ROLE_ID IN ('Supplier', 'Company') " +
            "                 GROUP BY PARTY_ID " +
            "                HAVING COUNT(PARTY_ID) > 1) " +
            "           AND A.ID = J.PARTNER_ID) " +
            " AND J.PARTNER_ID NOT IN (SELECT PARTNER_ID FROM ACC_CONTRACT WHERE CONTRACT_TYPE = 'Contract.contractType.platform' AND VALID_PERIOD >= GET_DATE() AND STATE IN ('approve', 'extension')) ", nativeQuery = true)
    List<Contract> qryValidContract(@Param("companyId") List companyId, @Param("contractTypeId") String contractTypeId);

    @Query(value = "" +
            "select * " +
            "  from ACC_CONTRACT J " +
            " WHERE COMPANY_ID = :companyId " +
            "   and CONTRACT_TYPE = :contractTypeId " +
            "   and STATE in ('approve', 'extension') " +
            "   and TRUNC(VALID_PERIOD) >= TRUNC(SYSDATE) " +
            "   and PARTNER_ID in (:partnerId) " +
            "   AND NOT EXISTS " +
            " (SELECT 1 " + // 剔除内部合同
            "          FROM MAS_PARTY_ROLE A " +
            "         WHERE ROLE_ID = 'Supplier' " +
            "           AND EXISTS (SELECT COUNT(PARTY_ID), PARTY_ID " +
            "                  FROM MAS_PARTY_ROLE " +
            "                 WHERE PARTY_ID = A.PARTY_ID " +
            "                   AND ROLE_ID IN ('Supplier', 'Company') " +
            "                 GROUP BY PARTY_ID " +
            "                HAVING COUNT(PARTY_ID) > 1) " +
            "           AND A.ID = J.PARTNER_ID) ", nativeQuery = true)
    List<Contract> qryValidContractSupplier(@Param("companyId") String companyId, @Param("contractTypeId") String contractTypeId, @Param("partnerId") List partnerId);

    @Query(value = "" +
            "select * " +
            "  from ACC_CONTRACT J " +
            " WHERE COMPANY_ID = :companyId " +
            "   and CONTRACT_TYPE = :contractTypeId " +
            "   and STATE in ('approve', 'extension') " +
            "   and TRUNC(VALID_PERIOD) >= TRUNC(SYSDATE) " +
            "   AND NOT EXISTS " +
            " (SELECT 1 " + // 剔除内部合同
            "          FROM MAS_PARTY_ROLE A " +
            "         WHERE ROLE_ID = 'Supplier' " +
            "           AND EXISTS (SELECT COUNT(PARTY_ID), PARTY_ID " +
            "                  FROM MAS_PARTY_ROLE " +
            "                 WHERE PARTY_ID = A.PARTY_ID " +
            "                   AND ROLE_ID IN ('Supplier', 'Company') " +
            "                 GROUP BY PARTY_ID " +
            "                HAVING COUNT(PARTY_ID) > 1) " +
            "           AND A.ID = J.PARTNER_ID) ", nativeQuery = true)
    List<Contract> qryValidContractNotSupplier(@Param("companyId") String companyId, @Param("contractTypeId") String contractTypeId);

    @Query(value = "" +
            "select * " +
            "  from ACC_CONTRACT J " +
            " WHERE COMPANY_ID IN :companyId " +
            "   and CONTRACT_TYPE = :contractTypeId " +
            "   and STATE in ('approve', 'extension') " +
            "   and TRUNC(VALID_PERIOD) >= TRUNC(SYSDATE) " +
            "   and (PARTNER_ID LIKE %:partnerId% OR NAME LIKE %:contractName% OR ID LIKE %:contractId% " +
            "        OR EXISTS (SELECT 1 FROM MAS_PARTY H, MAS_PARTY_ROLE K WHERE H.ID = K.PARTY_ID AND K.ID = J.PARTNER_ID AND H.NAME LIKE %:partnerName%) ) " +
            "   and PARTNER_ID not in (:notinSupplier) " +
            "   AND NOT EXISTS " +
            " (SELECT 1 " + // 剔除内部合同
            "          FROM MAS_PARTY_ROLE A " +
            "         WHERE ROLE_ID = 'Supplier' " +
            "           AND EXISTS (SELECT COUNT(PARTY_ID), PARTY_ID " +
            "                  FROM MAS_PARTY_ROLE " +
            "                 WHERE PARTY_ID = A.PARTY_ID " +
            "                   AND ROLE_ID IN ('Supplier', 'Company') " +
            "                 GROUP BY PARTY_ID " +
            "                HAVING COUNT(PARTY_ID) > 1) " +
            "           AND A.ID = J.PARTNER_ID) " +
            " AND J.PARTNER_ID NOT IN (SELECT PARTNER_ID FROM ACC_CONTRACT WHERE CONTRACT_TYPE = 'Contract.contractType.platform' AND VALID_PERIOD >= GET_DATE() AND STATE IN ('approve', 'extension')) ", nativeQuery = true)
    List<Contract> qrylikeContract(@Param("companyId") List companyId, @Param("contractTypeId") String contractTypeId, @Param("partnerId") String partnerId, @Param("contractName") String contractName,
                                   @Param("contractId") String contractId, @Param("partnerName") String partnerName, @Param("notinSupplier") List notinSupplier);

    @Query("SELECT t from Contract t " +
            " where t.partner.id in (:partnerId) " +
            " and t.validPeriod > :currentTime " +
            " and t.approveDate is not null " +
            " and t.repealDate is null")
    List<Contract> accdoringQrySupplierContract(@Param("partnerId") List partnerId,
                                              @Param("currentTime") Date currentTime);

    @Query(value = "SELECT * FROM ACC_CONTRACT WHERE CREATE_DATE = (SELECT MAX(CREATE_DATE) FROM ACC_CONTRACT WHERE ID LIKE %:type%)",nativeQuery = true)
    List<Contract> findMaxId(@Param("type") String type);

    @Query("SELECT t from Contract t " +
            " where t.partner.id = :partnerId " +
            " and t.company.id = :companyId " +
            " and t.state in ('approve','extension') " +
            " and t.contractType.id = :contractTypeId")
    List<Contract> findByCompanyAndPartner(@Param("companyId") String companyId,@Param("partnerId") String partnerId,@Param("contractTypeId") String contractTypeId);

    @Query(value = "select t.* from acc_contract t where (t.id like %:value% or t.name like %:value%) " +
            "  and exists (select * from mas_party_role a where a.id = t.partner_id and not exists " +
            "(select * from mas_party_role b where b.party_id = a.party_id and b.role_id = 'Company') ) ", nativeQuery = true)
    List<Contract> findContractNotInternalSupplier(@Param("value")String value);

    @Query(value = "" +
            "select * " +
            "  from ACC_CONTRACT J " +
            " WHERE COMPANY_ID = :companyId " +
            "   and CONTRACT_TYPE = :contractTypeId " +
            "   and STATE in ('approve', 'extension') " +
            "   and TRUNC(VALID_PERIOD) >= TRUNC(SYSDATE) " +
            "   and PARTNER_ID in (:partnerId) ", nativeQuery = true)
    List<Contract> qryValidContract(@Param("companyId") String companyId, @Param("contractTypeId") String contractTypeId, @Param("partnerId") String partnerId);

    @Query(value = "" +
            "select * " +
            "  from ACC_CONTRACT J " +
            " WHERE COMPANY_ID = :companyId " +
            "   and CONTRACT_TYPE = :contractTypeId " +
            "   and STATE in ('approve', 'extension') " +
            "   and TRUNC(VALID_PERIOD) >= TRUNC(SYSDATE) AND TRUNC(VALID_FROM) <= TRUNC(SYSDATE) ", nativeQuery = true)
    List<Contract> accordingCompanyValidContract(@Param("companyId") String companyId, @Param("contractTypeId") String contractTypeId);

    @Query(value = "" +
            "select * " +
            "  from ACC_CONTRACT J " +
            " WHERE COMPANY_ID = :companyId " +
            "   and CONTRACT_TYPE = :contractTypeId " +
            "   AND PARTNER_ID IN (:suppliers) " +
            "   and STATE in ('approve', 'extension') " +
            "   and TRUNC(VALID_PERIOD) >= TRUNC(SYSDATE) AND TRUNC(VALID_FROM) <= TRUNC(SYSDATE) ", nativeQuery = true)
    List<Contract> accordingCompanyAndSupplierValidContract(@Param("companyId") String companyId, @Param("contractTypeId") String contractTypeId, List<PartyRole> suppliers);


    @Query("SELECT t from Contract t " +
            " where t.company.id = :companyId " +
            " and t.partner.id = :partnerId " +
            " and t.validPeriod > :currentTime " +
            " and t.approveDate is not null and t.contractType.id ='Contract.contractType.customer' and (t.state='approve' or t.state='extension')")
    Contract findByCompanyAndPartner(@Param("companyId") String companyId, @Param("partnerId") String partnerId, @Param("currentTime") Date currentTime);

    @Query("SELECT t from Contract t WHERE t.partner = :partner and t.cooperation.id = :cooperationId " +
            " and t.contractType.id = :contractTypeId and t.state = 'approve' " +
            " and t.company.id = :companyId ")
    List<Contract> findByPartnerAndCooperation(@Param("partner") PartyRole partner,@Param("cooperationId") String cooperationId,
                                               @Param("contractTypeId")String contractTypeId,@Param("companyId")String companyId);

    @Query("SELECT t from Contract t " +
            " where t.company.id = :companyId and t.partner.id = :partnerId and t.validPeriod > sysdate and t.state not in ('delete','repeal')" +
            " order by t.createDate")
    List<Contract> findvalid(@Param("companyId") String companyId, @Param("partnerId") String partnerId);
	
	    @Query("SELECT t from Contract t WHERE t.partner = :partner and t.validPeriod > sysdate and contractType.id = 'Contract.contractType.platform'")
    List<Contract> findValidPlatform(@Param("partner") PartyRole partner);

    @Query("SELECT t from Contract t WHERE t.partner = :partner and t.validPeriod > sysdate and contractType.id = 'Contract.contractType.supplier'")
    List<Contract> findValidPlatformSupplier(@Param("partner") PartyRole partner);
    @Query("SELECT t from Contract t WHERE t.partner = :partner and t.state = 'approve' and contractType.id = 'Contract.contractType.supplier'")
    List<Contract> findPartyRoleStateSupplier(@Param("partner") PartyRole partner);

    @Query("SELECT t from Contract t WHERE t.partner = :partner and t.validPeriod >= sysdate and t.state = 'approve'  and contractType.id = 'Contract.contractType.supplier'")
    List<Contract> findValidPlatformSupplierState(@Param("partner") PartyRole partner);
    @Query("SELECT t from Contract t WHERE t.partner = :partner and t.validPeriod >= sysdate and t.state = 'approve' and contractType.id = 'Contract.contractType.platform'")
    List<Contract> findValidPlatformState(@Param("partner") PartyRole partner);

    @Query("select T from Contract T where T.validPeriod > :endDate AND T.state = :state AND T.settlementType.id IN ('001416','001420') AND T.contractType.id IN ('Contract.contractType.supplier','Contract.contractType.platform')")
    List<Contract> findAllByState(@Param("endDate") Date endDate,@Param("state") String state);
}
